﻿Imports System.Data.SqlClient

Public Class frmRetailApprove

    Sub FillPendingTransactions()
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim cmd As New SqlCommand("Select distinct MoveNo,InsuranceCompany " & _
                                      "From Retail Where Done=0 and Branch=N'" & Branch & "'", cnn)
            Dim Reader As SqlDataReader
            Dim X As Integer = 1

            Clear()
            Me.ListPendingTrans.Items.Clear()

            cnn.Open()
            Reader = cmd.ExecuteReader
            While (Reader.Read)
                Dim Item As New ListViewItem
                Item.Text = Reader.Item("MoveNo")
                Item.SubItems.Add(X)
                Item.SubItems.Add(Reader.Item("InsuranceCompany"))

                Me.ListPendingTrans.Items.Add(Item)
                X += 1
            End While
            cnn.Close()

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Sub Clear()
        Me.CombInsurComp.Clear()
        Me.GridRetail.Rows.Clear()
        Me.GridVoucher.Rows.Clear()

    End Sub

    Private Sub ChAccPath_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ChAccPath.CheckedChanged
        If Me.ChAccPath.Checked = True Then
            Me.DGColAcc1.Visible = True
            Me.DGColAcc2.Visible = True
            Me.DGColAcc3.Visible = True
        ElseIf Me.ChAccPath.Checked = False Then
            Me.DGColAcc1.Visible = False
            Me.DGColAcc2.Visible = False
            Me.DGColAcc3.Visible = False
        End If
    End Sub

    Private Sub GridVoucher_CellClick_1(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles GridVoucher.CellClick
        If e.ColumnIndex = Me.DGColSearch.Index Then
            Try
                Dim a As New frmSelectAccount
                a.ShowDialog()

                If SelAccNo <> "" Then
                    Me.Cursor = Cursors.WaitCursor

                    'Get acc path
                    Dim AccNames(3) As String
                    AccNames = GetAccNames(SelAccNo)

                    Me.GridVoucher.CurrentRow.Cells(Me.DGColAccNo.Index).Value = SelAccNo
                    Me.GridVoucher.CurrentRow.Cells(Me.DGColAcc1.Index).Value = AccNames(0)
                    Me.GridVoucher.CurrentRow.Cells(Me.DGColAcc2.Index).Value = AccNames(1)
                    Me.GridVoucher.CurrentRow.Cells(Me.DGColAcc3.Index).Value = AccNames(2)
                    Me.GridVoucher.CurrentRow.Cells(Me.DGColAcc4.Index).Value = AccNames(3)

                    Me.Cursor = Cursors.Default
                End If
            Catch ex As Exception
                Me.Cursor = Cursors.Default
                MsgBox(ex.ToString)
            End Try

        ElseIf e.ColumnIndex = Me.DGColDelete.Index Then
            If e.RowIndex < (Me.GridVoucher.Rows.Count - 1) Then
                Me.GridVoucher.Rows.RemoveAt(e.RowIndex)
            End If
        End If
    End Sub

    Sub BuildRetailVoucher()
        Try
            Me.Cursor = Cursors.WaitCursor

            Me.GridVoucher.Rows.Clear()

            Dim ClientAccNo, InsurCompPremAccNo, RevCommissionAccNo, InsurCompAccruedCommissionAccNo, ProducerExpensesAccNo, _
                SamaAccNo, SamaAccNoExpenses, RetailDiscountAccNo As String
            Dim AccNames(3), DescrPrem, DescrComm As String
            Dim CompanyCommission, SAMASupervFees As Double

            '  For Each row As DataGridViewRow In Me.GridRetail.Rows

            Dim TotalBatch, Discount, GrandTotal As Double
            TotalBatch = Math.Abs(CDbl(Me.GridRetail.CurrentRow.Cells(Me.DGColTotalBatch.Index).Value)).ToString("N2")
            Discount = Math.Abs(CDbl(Me.GridRetail.CurrentRow.Cells(Me.DGColDiscount.Index).Value)).ToString("N2")
            GrandTotal = TotalBatch + Discount

            CompanyCommission = Math.Abs(CDbl(Me.GridRetail.CurrentRow.Cells(Me.DGColComm.Index).Value)).ToString("N2")
            SAMASupervFees = CompanyCommission * 1 / 100

            ClientAccNo = GetRetailClientAccNo(Me.GridRetail.CurrentRow.Cells(Me.DGColProducerName.Index).Value)
            InsurCompAccruedCommissionAccNo = GetInsurCompAccruedCommAccNo(Me.ListPendingTrans.SelectedItems(0).SubItems(2).Text)
            InsurCompPremAccNo = GetInsurCompPremAccNo(Me.ListPendingTrans.SelectedItems(0).SubItems(2).Text)
            SamaAccNo = GetFinancialSettingsAccNo("SAMA Supervisory Fees")
            SamaAccNoExpenses = GetFinancialSettingsAccNo("SAMA Supervisory Fees - Expenses")
            ProducerExpensesAccNo = GetFinancialSettingsAccNo("Producers Commissions - Expenses")
            RetailDiscountAccNo = GetRetailDiscountAccNo("Retail Discount - Expenses")

            If CDbl(Me.GridRetail.CurrentRow.Cells(Me.DGColTotalBatch.Index).Value) > 0 Then

                DescrPrem = Me.ListPendingTrans.SelectedItems(0).SubItems(2).Text & ", Client# " & Me.GridRetail.CurrentRow.Cells(Me.DGColProducerCode.Index).Value & ""

            End If

            '1) Client vs Insurance Company Premium
            If CDbl(Me.GridRetail.CurrentRow.Cells(Me.DGColTotalBatch.Index).Value) > 0 Then
                AccNames = GetAccNames(ClientAccNo)
                Me.GridVoucher.Rows.Add({ClientAccNo, AccNames(0), AccNames(1), AccNames(2), AccNames(3), "Search....", DescrPrem,
                                         TotalBatch.ToString("N2"), "0.00"})
                AccNames = GetAccNames(RetailDiscountAccNo)

                Me.GridVoucher.Rows.Add({RetailDiscountAccNo, AccNames(0), AccNames(1), AccNames(2), AccNames(3), "Search....", DescrPrem,
                                        Discount.ToString("N2"), "0.00"})

                AccNames = GetAccNames(InsurCompPremAccNo)
                Me.GridVoucher.Rows.Add({InsurCompPremAccNo, AccNames(0), AccNames(1), AccNames(2), AccNames(3), "Search....", DescrPrem,
                                         "0.00", GrandTotal.ToString("N2")})
            Else
                AccNames = GetAccNames(ClientAccNo)
                Me.GridVoucher.Rows.Add({ClientAccNo, AccNames(0), AccNames(1), AccNames(2), AccNames(3), "Search....", DescrPrem,
                                         "0.00", TotalBatch.ToString("N2")})

                AccNames = GetAccNames(InsurCompPremAccNo)
                Me.GridVoucher.Rows.Add({InsurCompPremAccNo, AccNames(0), AccNames(1), AccNames(2), AccNames(3), "Search....", DescrPrem,
                                         TotalBatch.ToString("N2"), "0.00"})
            End If

            '2) Insurance Company vs Commission and discount
            If CDbl(Me.GridRetail.CurrentRow.Cells(Me.DGColComm.Index).Value) > 0 Then
                AccNames = GetAccNames(InsurCompAccruedCommissionAccNo)
                Me.GridVoucher.Rows.Add({InsurCompAccruedCommissionAccNo, AccNames(0), AccNames(1), AccNames(2), AccNames(3), "Search....", DescrComm,
                                         CompanyCommission.ToString("N2"), "0.00"})

                AccNames = GetAccNames(RevCommissionAccNo)
                Me.GridVoucher.Rows.Add({RevCommissionAccNo, AccNames(0), AccNames(1), AccNames(2), AccNames(3), "Search....", DescrComm,
                                         "0.00", CompanyCommission.ToString("N2")})
            Else
                AccNames = GetAccNames(InsurCompAccruedCommissionAccNo)
                Me.GridVoucher.Rows.Add({InsurCompAccruedCommissionAccNo, AccNames(0), AccNames(1), AccNames(2), AccNames(3), "Search....", DescrComm,
                                         "0.00", CompanyCommission.ToString("N2")})

                AccNames = GetAccNames(RevCommissionAccNo)
                Me.GridVoucher.Rows.Add({RevCommissionAccNo, AccNames(0), AccNames(1), AccNames(2), AccNames(3), "Search....", DescrComm,
                                         CompanyCommission.ToString("N2"), "0.00"})
            End If

            '3) Commission vs SAMA Supervisory Fees
            If CDbl(Me.GridRetail.CurrentRow.Cells(Me.DGColComm.Index).Value) > 0 Then
                AccNames = GetAccNames(SamaAccNoExpenses)
                Me.GridVoucher.Rows.Add({SamaAccNoExpenses, AccNames(0), AccNames(1), AccNames(2), AccNames(3), "Search....", DescrComm,
                                         SAMASupervFees.ToString("N2"), "0.00"})

                AccNames = GetAccNames(SamaAccNo)
                Me.GridVoucher.Rows.Add({SamaAccNo, AccNames(0), AccNames(1), AccNames(2), AccNames(3), "Search....", DescrComm,
                                         "0.00", SAMASupervFees.ToString("N2")})
            Else
                AccNames = GetAccNames(SamaAccNoExpenses)
                Me.GridVoucher.Rows.Add({SamaAccNoExpenses, AccNames(0), AccNames(1), AccNames(2), AccNames(3), "Search....", DescrComm,
                                         "0.00", SAMASupervFees.ToString("N2")})

                AccNames = GetAccNames(SamaAccNo)
                Me.GridVoucher.Rows.Add({SamaAccNo, AccNames(0), AccNames(1), AccNames(2), AccNames(3), "Search....", DescrComm,
                                         SAMASupervFees.ToString("N2"), "0.00"})
            End If

            '4)Bank and Producer

            AccNames = GetAccNames(ClientAccNo)
            Me.GridVoucher.Rows.Add({ClientAccNo, AccNames(0), AccNames(1), AccNames(2), AccNames(3), "Search....", DescrPrem,
                                     "0.00", TotalBatch.ToString("N2")})

            AccNames = GetAccNames(InsurCompPremAccNo)
            Me.GridVoucher.Rows.Add({InsurCompPremAccNo, AccNames(0), AccNames(1), AccNames(2), AccNames(3), "Search....", DescrPrem,
                                     TotalBatch.ToString("N2"), "0.00"})

            '  Next
            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub GridVoucher_CellEndEdit(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles GridVoucher.CellEndEdit
        If e.ColumnIndex = Me.DGColAccNo.Index Then
            Try
                If Me.GridVoucher.CurrentCell.Value <> "" Then
                    Me.Cursor = Cursors.WaitCursor

                    'Get acc path
                    Dim AccNames(3) As String
                    AccNames = GetAccNames(Me.GridVoucher.CurrentCell.Value)

                    Me.GridVoucher.CurrentRow.Cells(Me.DGColAcc1.Index).Value = AccNames(0)
                    Me.GridVoucher.CurrentRow.Cells(Me.DGColAcc2.Index).Value = AccNames(1)
                    Me.GridVoucher.CurrentRow.Cells(Me.DGColAcc3.Index).Value = AccNames(2)
                    Me.GridVoucher.CurrentRow.Cells(Me.DGColAcc4.Index).Value = AccNames(3)

                    Me.Cursor = Cursors.Default
                Else
                    Me.GridVoucher.CurrentRow.Cells(Me.DGColAcc1.Index).Value = ""
                    Me.GridVoucher.CurrentRow.Cells(Me.DGColAcc2.Index).Value = ""
                    Me.GridVoucher.CurrentRow.Cells(Me.DGColAcc3.Index).Value = ""
                    Me.GridVoucher.CurrentRow.Cells(Me.DGColAcc4.Index).Value = ""
                End If
            Catch ex As Exception
                Me.Cursor = Cursors.Default
                MsgBox(ex.ToString)
            End Try

        ElseIf e.ColumnIndex = Me.DGColDebit.Index Then
            If Me.GridVoucher.CurrentCell.Value = "" Then
                Me.GridVoucher.CurrentCell.Value = "0.00"
            Else
                Try
                    Me.GridVoucher.CurrentCell.Value = CDbl(Me.GridVoucher.CurrentCell.Value).ToString("N2")
                    If CDbl(Me.GridVoucher.CurrentCell.Value) <> 0 Then
                        Me.GridVoucher.CurrentRow.Cells(Me.DGColCredit.Index).Value = "0.00"
                    End If
                Catch ex As Exception
                    Me.GridVoucher.CurrentCell.Value = "0.00"
                End Try
            End If

        ElseIf e.ColumnIndex = Me.DGColCredit.Index Then
            If Me.GridVoucher.CurrentCell.Value = "" Then
                Me.GridVoucher.CurrentCell.Value = "0.00"
            Else
                Try
                    Me.GridVoucher.CurrentCell.Value = CDbl(Me.GridVoucher.CurrentCell.Value).ToString("N2")
                    If CDbl(Me.GridVoucher.CurrentCell.Value) <> 0 Then
                        Me.GridVoucher.CurrentRow.Cells(Me.DGColDebit.Index).Value = "0.00"
                    End If
                Catch ex As Exception
                    Me.GridVoucher.CurrentCell.Value = "0.00"
                End Try
            End If

        End If

        Calculate()
        ValidateGrid()
    End Sub

    Sub CalculateTotal()
        Try
            Me.Cursor = Cursors.WaitCursor

            For Each Row As DataGridViewRow In Me.GridRetail.Rows
                Row.Cells(Me.DGColTotalBatch.Index).Value = CDbl(Row.Cells(Me.DGColNoOfPolicies.Index).Value) * _
                    (CDbl(Row.Cells(Me.DGColGivenPrice.Index).Value))
                Row.Cells(Me.DGColDiscount.Index).Value = CDbl(Row.Cells(Me.DGColNoOfPolicies.Index).Value) * _
                  (CDbl(Row.Cells(Me.DGColActualPrice.Index).Value) - CDbl(Row.Cells(Me.DGColGivenPrice.Index).Value))

            Next
            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
        End Try
    End Sub

    Private Sub GridRetail_CellEndEdit(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles GridRetail.CellEndEdit

        If e.ColumnIndex = Me.DGColProducerCode.Index Then
            Try
                If Me.GridRetail.CurrentCell.Value <> "" Then
                    Me.Cursor = Cursors.WaitCursor

                    'Get Client Name
                    Dim ClientName As String
                    ClientName = GetRetailClientName(Me.GridRetail.CurrentCell.Value)

                    Me.GridRetail.CurrentRow.Cells(Me.DGColProducerName.Index).Value = ClientName

                    Me.Cursor = Cursors.Default
                Else
                    Me.GridRetail.CurrentRow.Cells(Me.DGColProducerName.Index).Value = ""
                End If
            Catch ex As Exception
                Me.Cursor = Cursors.Default
                MsgBox(ex.ToString)
            End Try
        End If

        If e.ColumnIndex = Me.DGColNoOfPolicies.Index Then
            Try
                Me.GridRetail.CurrentCell.Value = CDbl(Me.GridRetail.CurrentCell.Value).ToString("N2")
                CalculateTotal()
            Catch ex As Exception
                Me.GridRetail.CurrentCell.Value = "1"
            End Try

        ElseIf e.ColumnIndex = Me.DGColGivenPrice.Index Then
            Try
                Me.GridRetail.CurrentCell.Value = CDbl(Me.GridRetail.CurrentCell.Value).ToString("N2")
            Catch ex As Exception
                Me.GridRetail.CurrentCell.Value = "0.00"
            End Try
        ElseIf e.ColumnIndex = Me.DGColActualPrice.Index Then
            Try
                Me.GridRetail.CurrentCell.Value = CDbl(Me.GridRetail.CurrentCell.Value).ToString("N2")
            Catch ex As Exception
                Me.GridRetail.CurrentCell.Value = "0.00"
            End Try
        ElseIf e.ColumnIndex = Me.DGColReject.Index Then
            Try
                Me.GridRetail.CurrentCell.Value = CDbl(Me.GridRetail.CurrentCell.Value).ToString("N2")
            Catch ex As Exception
                Me.GridRetail.CurrentCell.Value = "0.00"
            End Try
        End If

        CalculateTotal()

    End Sub

    Sub ValidateGrid()
        Try
            Me.Cursor = Cursors.WaitCursor

            For Each row As DataGridViewRow In Me.GridVoucher.Rows
                If row.Cells(4).Value = "" Then
                    row.Cells(4).Style.BackColor = Color.Salmon
                Else
                    row.Cells(4).Style.BackColor = row.DefaultCellStyle.BackColor
                End If

                If CDbl(row.Cells(DGColDebit.Index).Value) = 0 And CDbl(row.Cells(DGColCredit.Index).Value) = 0 Then
                    row.Cells(DGColDebit.Index).Style.BackColor = Color.Salmon
                    row.Cells(DGColCredit.Index).Style.BackColor = Color.Salmon

                ElseIf CDbl(row.Cells(DGColDebit.Index).Value) = 0 Or CDbl(row.Cells(DGColCredit.Index).Value) = 0 Then
                    row.Cells(DGColDebit.Index).Style.BackColor = row.DefaultCellStyle.BackColor
                    row.Cells(DGColCredit.Index).Style.BackColor = row.DefaultCellStyle.BackColor
                End If
            Next

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
        End Try
    End Sub

    Sub Calculate()
        If Me.GridVoucher.Rows.Count = 0 Then
            Me.txtCrd.Text = "0"
            Me.txtDep.Text = "0"
            Me.txtBalance.Text = "0"
        Else
            Try
                Dim Crd, Dep As Double
                Dim i As Integer

                ' Iterate through a dictionary
                For i = 0 To Me.GridVoucher.Rows.Count - 1
                    Dep = Dep + Me.GridVoucher.Rows(i).Cells(Me.DGColDebit.Index).Value
                    Crd = Crd + Me.GridVoucher.Rows(i).Cells(Me.DGColCredit.Index).Value
                Next

                If Crd = 0 Then
                    Me.txtCrd.Text = "0.00"
                Else
                    Me.txtCrd.Text = Format(Crd, "##,###.##")
                End If

                If Dep = 0 Then
                    Me.txtDep.Text = "0.00"
                Else
                    Me.txtDep.Text = Format(Dep, "##,###.##")
                End If

                If Crd - Dep = 0 Then
                    Me.txtBalance.Text = "0.00"
                Else
                    Me.txtBalance.Text = Format(CDbl(Crd - Dep), "##,###.##")
                End If
            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try
        End If
    End Sub

    Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
        Me.Close()
    End Sub

    Private Sub ListPendingTrans_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ListPendingTrans.SelectedIndexChanged
        Try
            Clear()
            If Me.ListPendingTrans.SelectedItems.Count > 0 Then
                Me.Cursor = Cursors.WaitCursor

                Dim cmd As New SqlCommand("Select * From Retail Where MoveNo=" & Me.ListPendingTrans.SelectedItems(0).Text, cnn)
                Dim Reader As SqlDataReader

                cnn.Open()
                Reader = cmd.ExecuteReader
                While (Reader.Read)

                    Me.GridRetail.Rows.Add(New String() {Reader.Item("SNo"), Reader.Item("BatchNo"), Reader.Item("ClientCode"), Reader.Item("ClientName"), _
                                                         Reader.Item("Reject"), Reader.Item("PoliciesNo"), Reader.Item("ActualPrice"), Reader.Item("GivenPrice"), _
                                                          Reader.Item("Comm"), Reader.Item("TotalBatch"), Reader.Item("Discount")})

                    Me.CombInsurComp.Text = Me.ListPendingTrans.SelectedItems(0).SubItems(2).Text

                End While
                Reader.Close()
                cnn.Close()

                BuildRetailVoucher()
                Calculate()
                ValidateGrid()

                Me.ListPendingTrans.Focus()

                Me.Cursor = Cursors.Default
            End If
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub frmRetailApprove_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Me.WindowState = FormWindowState.Maximized
        FillPendingTransactions()
    End Sub

    Private Sub btnApprove_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnApprove.Click
        Me.ErrProv.Clear()
        If Me.ListPendingTrans.SelectedItems.Count = 0 Then
            MsgBox("Please select a policy/endorsement from the list")
            Exit Sub

        ElseIf Me.GridVoucher.Rows.Count = 0 Then
            MsgBox("Please enter voucher details")

        ElseIf CDbl(Me.txtCrd.Text - Me.txtDep.Text) <> 0 Then
            Me.ErrProv.SetError(Me.txtBalance, "Please complete the voucher")

        ElseIf ValidateDate(Me.DTPTrans.Value) = False Then
            Me.ErrProv.SetError(Me.DTPTrans, "Date isn't accepted")

        Else
            Try
                Me.Cursor = Cursors.WaitCursor

                Me.Cursor = Cursors.WaitCursor

                'Validate rows
                Dim Valid As Boolean = True
                For Each row As DataGridViewRow In Me.GridVoucher.Rows
                    If row.Index <> (Me.GridVoucher.Rows.Count - 1) Then
                        If row.Cells(4).Style.BackColor = Color.Salmon OrElse row.Cells(6).Value = "" _
                      OrElse row.Cells(7).Value = "" OrElse row.Cells(8).Value = "" OrElse row.Cells(9).Style.BackColor = Color.Salmon Then
                            row.ErrorText = "Please fill in all fields"
                            Valid = False
                        Else
                            row.ErrorText = ""
                        End If
                    End If
                Next

                If Valid = False Then
                    Me.Cursor = Cursors.Default
                    Exit Sub
                End If

                Dim MoveNo As Integer
                Dim Year As Integer
                Dim cmd As New SqlCommand
                Dim Trans As SqlTransaction

                cnn.Open()
                cmd.Connection = cnn
                Trans = cnn.BeginTransaction
                cmd.Transaction = Trans

                'Get Year
                cmd.CommandText = "Select Year(GetDate())"
                Year = CInt(cmd.ExecuteScalar)

                'Get Move No.
                cmd.CommandText = "Select IsNull(Max(MoveNo),0) From Transactions Where Branch=N'" & Branch & "' and Year(TransDate)=" & Me.DTPTrans.Value.Year
                MoveNo = CInt(cmd.ExecuteScalar) + 1

                For Each row As DataGridViewRow In Me.GridVoucher.Rows
                    If row.Index <> (Me.GridVoucher.Rows.Count - 1) Then
                        cmd.CommandText = "Insert into Transactions (TransType,MoveNo,AccNo,Acc1,Acc2,Acc3,Acc4,Descr,TotalOut,TotalIn," & _
                                          "SavedUser,TransDate,Branch,ApprovedUser,ApprovedDate) Values (@TransType,@MoveNo,@AccNo,@Acc1," & _
                                          "@Acc2,@Acc3,@Acc4,@Descr,@TotalOut,@TotalIn,@SavedUser,@TransDate,@Branch,@ApprovedUser,GetDate())"

                        cmd.Parameters.Clear()
                        cmd.Parameters.AddWithValue("@TransType", "Journal Voucher")
                        cmd.Parameters.AddWithValue("@MoveNo", MoveNo)
                        cmd.Parameters.AddWithValue("@AccNo", row.Cells(Me.DGColAccNo.Index).Value)
                        cmd.Parameters.AddWithValue("@Acc1", row.Cells(Me.DGColAcc1.Index).Value)
                        cmd.Parameters.AddWithValue("@Acc2", row.Cells(Me.DGColAcc2.Index).Value)
                        cmd.Parameters.AddWithValue("@Acc3", row.Cells(Me.DGColAcc3.Index).Value)
                        cmd.Parameters.AddWithValue("@Acc4", row.Cells(Me.DGColAcc4.Index).Value)
                        cmd.Parameters.AddWithValue("@Descr", row.Cells(Me.DGColDescr.Index).Value)
                        cmd.Parameters.AddWithValue("@Branch", Branch)
                        cmd.Parameters.AddWithValue("@TotalOut", CDbl(row.Cells(Me.DGColDebit.Index).Value))
                        cmd.Parameters.AddWithValue("@TotalIn", CDbl(row.Cells(Me.DGColCredit.Index).Value))
                        cmd.Parameters.AddWithValue("@SavedUser", CurrentUser)
                        cmd.Parameters.AddWithValue("@TransDate", Me.DTPTrans.Value)
                        cmd.Parameters.AddWithValue("@ApprovedUser", CurrentUser)
                        cmd.ExecuteNonQuery()
                    End If
                Next

                'Update Pol/End
                cmd.CommandText = "Update Retail Set Done=1,FinApprovedUser=@FinApprovedUser,FinApprovedDate=GetDate() " & _
                                   "Where SNo=" & Me.ListPendingTrans.SelectedItems(0).Text
                cmd.Parameters.Clear()
                cmd.Parameters.AddWithValue("@FinApprovedUser", CurrentUser)
                cmd.ExecuteNonQuery()

                Trans.Commit()
                cnn.Close()

                MsgBox("Approved Successfully")


                PrintVoucher(Branch, MoveNo, Me.DTPTrans.Value.Year)

                FillPendingTransactions()
                Clear()

                Me.Cursor = Cursors.Default
            Catch ex As Exception
                Me.Cursor = Cursors.Default
                If cnn.State = ConnectionState.Open Then
                    cnn.Close()
                End If
                MsgBox(ex.ToString)
            End Try
        End If
    End Sub

    Private Sub GridRetail_CellMouseDoubleClick(sender As Object, e As System.Windows.Forms.DataGridViewCellMouseEventArgs) Handles GridRetail.CellMouseDoubleClick
        For Each Row As DataGridViewRow In Me.GridRetail.Rows
            If Not Row.IsNewRow Then
                BuildRetailVoucher()
                Calculate()
                ValidateGrid()
            End If
        Next
       
    End Sub

    Private Sub GridRetail_SelectionChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles GridRetail.SelectionChanged
        If Me.GridRetail.CurrentRow.Index > 0 Then
            'cnn.Close()
           
        End If

    End Sub

End Class